<?php
namespace App\Job;

use Hyperf\AsyncQueue\Job;
use Hyperf\DbConnection\Db;
use Hyperf\Utils\ApplicationContext;
use App\Service\ArticleService;
use App\Helper\SeoHelper;
use Hyperf\Logger\LoggerFactory;

class CategoryJob extends Job
{
    protected $dataId;
    protected $loggerFactory;

    /**
     * @var ArticleService
     */
    protected $articleService;

    public function __construct(int $dataId)
    {
        $this->dataId = $dataId;
    }

    public function handle()
    {
        // 获取日志对象
        $loggerFactory = ApplicationContext::getContainer()->get(LoggerFactory::class);
        $logger = $loggerFactory->get('category', 'category');

        $logger->info("处理分类任务，ID：" . $this->dataId);

        try {
            $irugbyshop_conn = Db::connection('irugbyshop');
            $rball_conn = Db::connection('rball');

            $category_data = $irugbyshop_conn->selectOne("SELECT
                    c.category_id, c.image, c.parent_id, c.top, c.column, c.sort_order, c.status,
                    cd.language_id, cd.name, cd.description
                FROM oc_category c
                JOIN oc_category_description cd ON c.category_id = cd.category_id
                WHERE c.category_id = :cid LIMIT 1",
                ['cid' => $this->dataId]
            );

            if (!$category_data) {
                $logger->warning("未找到分类", ['_id' => $this->dataId]);
                return;
            }

            $category_data = (array)$category_data; // 强制转数组，后续处理更安全
            $category_data['paths'] = [];

            $pathCursor = $irugbyshop_conn->cursor("SELECT * FROM oc_category_path WHERE category_id = :cid", [
                'cid' => $this->dataId
            ]);

            foreach ($pathCursor as $path) {
                $path_category_id = (int)$path->category_id;
                $path_path_id     = (int)$path->path_id;

                // 优化：用一个 SQL 联合查出两个名称，减少查询次数
                $names = $irugbyshop_conn->select("
                    SELECT category_id, name
                    FROM oc_category_description
                    WHERE category_id IN ($path_category_id, $path_path_id)
                ");

                $nameMap = [];
                if($names){
                    foreach ($names as $n) {
                        $nameMap[$n->category_id] = $n->name;
                    }
                }

                $category_data['paths'][] = [
                    'category_id'         => $path_category_id,
                    'category_name'       => (!empty($nameMap) && $nameMap[$path_category_id]) ? $nameMap[$path_category_id] : '',
                    'path_id'             => $path_path_id,
                    'path_category_name'  => (!empty($nameMap) && $nameMap[$path_path_id]) ? $nameMap[$path_path_id] : '',
                    'level'               => (int)$path->level,
                ];
            }

            // oc_category表
            $rball_conn->insert("
                        INSERT INTO oc_category 
                            (image, parent_id, top, `column`, sort_order, status, meta_robots, date_added, date_modified) 
                        VALUES 
                            (:c_image, :c_parent_id, :c_top, :c_column, :c_sort_order, :c_sort_status, :c_meta_robots, NOW(), NOW())
                    ", [
                'c_image' => $category_data["image"],
                'c_parent_id' => $category_data["parent_id"],
                'c_top' => $category_data["top"],
                'c_column' => $category_data["column"],
                'c_sort_order' => $category_data["sort_order"],
                'c_sort_status' => $category_data["status"],
                'c_meta_robots' => ''
            ]);

            //oc_category_description
            $categoryId = (int)$rball_conn->getPdo()->lastInsertId();
            $languages = [1, 2];
            $category_data_name = $category_data["name"] ?? '';

            foreach ($languages as $language_id) {
                $rball_conn->insert("
                        INSERT INTO oc_category_description 
                            (category_id, language_id, name, description, meta_title, meta_description, meta_keyword, seo_keyword, 
                             seo_h1, seo_h2, seo_h3) 
                        VALUES 
                            (:cd_category_id, :cd_language_id, :cd_name, :cd_description, :cd_meta_title, :cd_meta_description, :cd_meta_keyword,
                             :cd_seo_keyword, :cd_seo_h1, :cd_seo_h2, :cd_seo_h3)
                    ", [
                    'cd_category_id' => $categoryId,
                    'cd_language_id' => $language_id,
                    'cd_name' => $category_data_name,
                    'cd_description' => '',
                    'cd_meta_title' => '',
                    'cd_meta_description' => '',
                    'cd_meta_keyword' => '',
                    'cd_seo_keyword' => '',
                    'cd_seo_h1' => '',
                    'cd_seo_h2' => '',
                    'cd_seo_h3' => ''
                ]);
            }

            // oc_category_path
            $level = 0;
            foreach ( $category_data['paths'] as $result) {
                if($category_data_name == $result['category_name']){
                    $cp_category_id = $categoryId;
                }else{
                    $cp_category_id_data = $rball_conn->selectOne("SELECT category_id FROM oc_category_description WHERE name = :cdname AND language_id = 1 LIMIT 1",
                            ['cdname' => $result['category_name']]
                    );

                    $cp_category_id = $cp_category_id_data ? (int)$cp_category_id_data->category_id : 9999;
                }

                $cp_path_id_data = $rball_conn->selectOne("SELECT category_id FROM oc_category_description WHERE name = :cdname AND language_id = 1 LIMIT 1",
                    ['cdname' => $result['path_category_name']]
                );
                $cp_path_id = $cp_path_id_data ? (int)$cp_path_id_data->category_id : 9999;

                $rball_conn->insert("
                        INSERT INTO oc_category_path 
                            (category_id, path_id, level) 
                        VALUES 
                            (:cp_category_id, :cp_path_id, :cp_level)
                    ", [
                    'cp_category_id' => $cp_category_id,
                    'cp_path_id' => $cp_path_id,
                    'cp_level' => $level
                ]);

                $level++;
            }

            $rball_conn->insert("
                        INSERT INTO oc_category_path 
                            (category_id, path_id, level) 
                        VALUES 
                            (:cp_category_id, :cp_path_id, :cp_level)
                    ", [
                'cp_category_id' => $categoryId,
                'cp_path_id' => $categoryId,
                'cp_level' => $level
            ]);

            //oc_category_to_store
            $rball_conn->insert("
                        INSERT INTO oc_category_to_store 
                            (category_id, store_id) 
                        VALUES 
                            (:cts_category_id, :cts_store_id)
                    ", [
                'cts_category_id' => $categoryId,
                'cts_store_id' => 0
            ]);

            //oc_seo_url
            foreach ($languages as $language_id) {
                if(!empty($category_data_name)){
                    $seoUrl = SeoHelper::generateSeoUrl($category_data_name);

                    $rball_conn->insert("
                        INSERT INTO oc_seo_url 
                            (store_id, language_id, query, keyword) 
                        VALUES 
                            (:su_store_id,:su_language_id,:su_query,:su_keyword)
                    ", [
                        'su_store_id' => 0,
                        'su_language_id' => $language_id,
                        'su_query' => 'category_id='.$categoryId,
                        'su_keyword' => $seoUrl
                    ]);
                }
            }

            $logger->info("分类信息--：" . json_encode($category_data, JSON_UNESCAPED_UNICODE));
        } catch (\Throwable $e) {
            $logger->error('CategoryJob 错误：' . $e->getMessage() . ' | Trace: ' . $e->getTraceAsString());
            throw $e; // 需要记录日志后再抛出
        }
    }
}
